﻿# -*- coding: UTF-8 -*-
from xlrd import open_workbook
from xlwt import Workbook,easyxf
from xlwt import Utils
from xlutils.copy import copy
from datetime import datetime

thirdparty = 'E:/projects/xyh/trunk/'

def write_line(sheet,row,color=None):
	if color == None:
		style = easyxf('font :height 100 ; borders : bottom thick')
	else:
		style = easyxf('font :height 100 ; borders : bottom thick , bottom_colour ' + color)
	for col in range(0,10):
		sheet.write(row,col,None,style)

def write_bill(response,order):

	book = Workbook(encoding='UTF-8')
	sheet = book.add_sheet('Sheet 1')
	
	#插入Logo图片
	sheet.insert_bitmap(thirdparty + 'thirdparty/logo.bmp', 0, 0,x = 0, y = 0, scale_x = 1, scale_y = 0.7)
	
	
	
	style = easyxf('font :height 300,bold true')
	style1 = easyxf('font :height 300,bold true; alignment: indent 2')
	sheet.write(0,2,'深 圳 市 鑫 远 航 国 际 货 运 代 理 有 限 公 司',style)
	sheet.write(1,2,'SHENZHEN SAILING LOGISTICS CO.,LTD',style1)
	sheet.write(2,2,'深 圳 市 罗 湖 区 嘉 宾 路 爵 士 大 厦 1 6 A 0 6',style)
	sheet.write(3,2,'TEL:0755-25138880  FAX:0755-25901858',style1)
	sheet.write(4,2,'W   e   b  :  W  W  W . K   D   L   C  .  N  E  T',style)
	
	write_line(sheet,5,'dark_yellow')
		
	
	style = easyxf('font : height 250,bold true,underline single')
	sheet.write(7,3,'	** DEBIT NOTE **  ',style)
	
	#账单条目
	style = easyxf('font : height 210',num_format_str='YYYY-MM-DD')
	sheet.write(8,0,'客户名称：',style)
	sheet.write(8,2,order.client_company,style)
	sheet.write(8,5,'联系人：',style)
	sheet.write(8,6,order.client_person,style)
	
	sheet.write(9,0,'装货港：',style)
	sheet.write(9,2,order.orderhbl.loading_port,style)
	sheet.write(9,5,'目的港',style)
	sheet.write(9,6,order.orderhbl.destination_place,style)
	
	sheet.write(10,0,'船名/航次：',style)
	sheet.write(10,2,order.orderhbl.shipping_name_number,style)
	sheet.write(10,5,'ETD',style)
	sheet.col(6).width = 3000
	sheet.write(10,6,order.orderhbl.shipping_date,style)
	
	write_line(sheet,11)
	
	global_row = 12
	#############
	sheet.write(global_row,0,'B/L NO')
	sheet.write(global_row,2,'SHIPPER')
	sheet.write(global_row,5,'VOLUME')
	sheet.write(global_row,7,'COLLECT')
	global_row = global_row + 1
	sheet.write(global_row,0,order.order_code)
	sheet.write(global_row,2,order.orderhbl.shipper)
	sheet.write(global_row,5,order.orderhbl.volume)
	sheet.write(global_row,7,order.orderhbl.payment_way)
	global_row = global_row + 1
	
	summy = {}
	for bill in order.bill_set.order_by('bill_code').filter(type__exact=2):
		try:
			summy[str(bill.currency)] = summy[str(bill.currency)] + float(bill.total)
		except(KeyError):
			summy[str(bill.currency)] = float(bill.total)
		sheet.write(global_row,3,str(bill.bill_name) + "(" + str(bill.currency) + str(bill.amount) + '*' + str(bill.count) + ')')
		sheet.write(global_row,7,str(bill.currency) + str(bill.total))
		global_row = global_row + 1
	#############
	
	global_row = global_row + 1
	write_line(sheet,global_row)
#	sheet.write(global_row+1,1,'USD:RMB1:6.5 ')
	
	summyStr = ''
	for key in summy:
		summyStr = summyStr + key + str(summy[key]) + '   '
	sheet.write(global_row+1,6,'TOTAL:  ' + summyStr)
	
	sheet.write(global_row+3,0,'1、请核对此账单并确认回传。')
	sheet.write(global_row+4,0,'2、付款后请回传水单（注明提单号），传真：755-25901858。')
	sheet.write(global_row+5,0,'3、付款需全额到账，我司不承担任何银行手续费。')
	sheet.write(global_row+6,0,'4、实际付款单位与发票付款单位必须保持一致，否则视为未付款。')
	
	
	sheet.write(global_row+7,0,'银行全称：中国银行深圳分行')
	sheet.write(global_row+8,0,'账户全称：深圳市鑫远航国际货运代理有限公司')
	sheet.write(global_row+9,0,'人民币账户：741 957 928 454')
	sheet.write(global_row+10,0,'美金账户： 775 757 960 786')
	sheet.write(global_row+12,0,'开户行招商银行')
	sheet.write(global_row+13,0,'户名： 刘俊平')
	sheet.write(global_row+14,0,'账号：6225 8878 2464 9798')
	sheet.write(global_row+15,0,'农业银行')
	sheet.write(global_row+16,0,'户名：刘俊平')
	sheet.write(global_row+17,0,'农业银行卡号：95599 8012 02493 08210')
	sheet.write(global_row+19,0,'SHENZHEN SAILING LOGISTICS CO.,LTD    MADE BY :' + order.operator.name)
	
	book.save(response)

def write_hbl_international(response,order):
	orderhbl = order.orderhbl
	rb = open_workbook(thirdparty + 'thirdparty/HBL.xls',formatting_info=True)

	wb = copy(rb)
	ws = wb.get_sheet(1)
	
	style_right = easyxf('borders:right thin,right_colour blue')
	style_left = easyxf('borders:left thin,left_colour blue')
	a = Utils.cell_to_rowcol('A5')
	ws.write(a[0],a[1],orderhbl.shipper)
	
	a = Utils.cell_to_rowcol('AB5')
	ws.write(a[0],a[1],orderhbl.so,style_left)
	
	a = Utils.cell_to_rowcol('AP5')
	ws.write(a[0],a[1],order.order_code)
	
	a = Utils.cell_to_rowcol('A11')
	ws.write(a[0],a[1],orderhbl.consignee)
	
	a = Utils.cell_to_rowcol('AB11')
	ws.write(a[0],a[1],orderhbl.cargo_collection_name,style_left)
	
	a = Utils.cell_to_rowcol('A17')
	ws.write(a[0],a[1],orderhbl.notifier,style_right)
	
	style = easyxf('borders:right thin,right_colour blue,left dotted,left_colour blue')
	a = Utils.cell_to_rowcol('P23')
	ws.write(a[0],a[1],orderhbl.receipt_place,style)
	
	a = Utils.cell_to_rowcol('P25')
	ws.write(a[0],a[1],orderhbl.loading_port,style)
	
	a = Utils.cell_to_rowcol('A25')
	ws.write(a[0],a[1],orderhbl.shipping_name_number)
	
	a = Utils.cell_to_rowcol('A27')
	ws.write(a[0],a[1],orderhbl.unloading_port)
	
	a = Utils.cell_to_rowcol('P27')
	ws.write(a[0],a[1],orderhbl.destination_place,style)
	
	a = Utils.cell_to_rowcol('A34')
	ws.write(a[0],a[1],orderhbl.shipping_mark)
	
	a = Utils.cell_to_rowcol('Q34')
	ws.write(a[0],a[1],orderhbl.english_name)
	
	a = Utils.cell_to_rowcol('K34')
	ws.write(a[0],a[1],str(orderhbl.numbers) + str(orderhbl.packing_type))
	
	a = Utils.cell_to_rowcol('A32')
	ws.write(a[0],a[1],orderhbl.sealing_number)
	
	a = Utils.cell_to_rowcol('AO34')
	ws.write(a[0],a[1],str(orderhbl.gross_weight) + 'KGS')
	
	a = Utils.cell_to_rowcol('AV34')
	ws.write(a[0],a[1],str(orderhbl.volume) + 'CBM')
	
	a = Utils.cell_to_rowcol('A40')
	ws.write(a[0],a[1],orderhbl.info_summy)
	
	a = Utils.cell_to_rowcol('A61')
	ws.write(a[0],a[1],datetime.strftime(orderhbl.shipping_date,'%d %b %Y'))
	
	a = Utils.cell_to_rowcol('N56')
	ws.write(a[0],a[1],orderhbl.payment_way,style_left)
	
	
	
	wb.save(response)

def write_hbl(response,order):
	orderhbl = order.orderhbl
	rb = open_workbook(thirdparty + 'thirdparty/HBL_gl.xls',formatting_info=True)

	wb = copy(rb)
	ws = wb.get_sheet(0)
	
	style_right = easyxf('borders:right medium,right_colour black')
	style_left = easyxf('borders:left medium,left_colour black')
	
	a = Utils.cell_to_rowcol('A3')
	ws.write(a[0],a[1],orderhbl.shipper)
	
	a = Utils.cell_to_rowcol('H7')
	ws.write(a[0],a[1],orderhbl.so,style_left)
	
	a = Utils.cell_to_rowcol('H3')
	ws.write(a[0],a[1],order.order_code,style_left)
	
	a = Utils.cell_to_rowcol('A9')
	ws.write(a[0],a[1],orderhbl.consignee)
	
	a = Utils.cell_to_rowcol('H30')
	ws.write(a[0],a[1],orderhbl.cargo_collection_name,style_left)
	
	a = Utils.cell_to_rowcol('A15')
	ws.write(a[0],a[1],orderhbl.notifier)
	
	a = Utils.cell_to_rowcol('A19')
	ws.write(a[0],a[1],orderhbl.receipt_place)
	
	a = Utils.cell_to_rowcol('E19')
	ws.write(a[0],a[1],orderhbl.loading_port,style_left)
	
	a = Utils.cell_to_rowcol('A17')
	ws.write(a[0],a[1],orderhbl.shipping_name_number)
	
	a = Utils.cell_to_rowcol('A21')
	ws.write(a[0],a[1],orderhbl.unloading_port)
	
	a = Utils.cell_to_rowcol('E21')
	ws.write(a[0],a[1],orderhbl.destination_place,style_left)
	
	a = Utils.cell_to_rowcol('A23')
	ws.write(a[0],a[1],orderhbl.shipping_mark)
	
	a = Utils.cell_to_rowcol('F23')
	ws.write(a[0],a[1],orderhbl.english_name)
	
	a = Utils.cell_to_rowcol('J23')
	ws.write(a[0],a[1],str(orderhbl.gross_weight) + 'KGS')
	
	a = Utils.cell_to_rowcol('L23')
	ws.write(a[0],a[1],str(orderhbl.volume) + 'CBM')
	
	a = Utils.cell_to_rowcol('A26')
	ws.write(a[0],a[1],orderhbl.info_summy)
	
	a = Utils.cell_to_rowcol('A37')
	ws.write(a[0],a[1],datetime.strftime(orderhbl.shipping_date,'%d %b %Y'))
	
	a = Utils.cell_to_rowcol('C38')
	ws.write(a[0],a[1],datetime.strftime(orderhbl.issue_date,'%d %b %Y'),style_left)
	
	a = Utils.cell_to_rowcol('C30')
	ws.write(a[0],a[1],orderhbl.payment_way,style_left)
	
	a = Utils.cell_to_rowcol('H19')
	ws.write(a[0],a[1],orderhbl.sealing_number,style_left)
	
	
	wb.save(response)